"""
Database connection module using psycopg2 (NO ORM!)
Direct SQL queries only - as required by project
"""

import psycopg2
from psycopg2.extras import RealDictCursor
from config import Config

def get_db_connection():
    """
    Get a database connection
    Returns a psycopg2 connection object
    """
    try:
        conn = psycopg2.connect(**Config.get_db_config())
        return conn
    except Exception as e:
        print(f"Database connection error: {e}")
        raise

def execute_query(query, params=None, fetch=True, fetch_one=False):
    """
    Execute a SQL query
    
    Args:
        query: SQL query string
        params: Query parameters (tuple or dict)
        fetch: Whether to fetch results (True for SELECT, False for INSERT/UPDATE/DELETE)
        fetch_one: Fetch only one result (for queries that return single row)
    
    Returns:
        For SELECT: list of dictionaries or single dictionary
        For INSERT/UPDATE/DELETE: True on success
    """
    conn = None
    cursor = None
    
    try:
        conn = get_db_connection()
        cursor = conn.cursor(cursor_factory=RealDictCursor)
        
        cursor.execute(query, params)
        
        if fetch:
            if fetch_one:
                result = cursor.fetchone()
                conn.commit()  # Commit for INSERT...RETURNING queries
                return dict(result) if result else None
            else:
                results = cursor.fetchall()
                return [dict(row) for row in results]
        else:
            conn.commit()
            return True
            
    except Exception as e:
        if conn:
            conn.rollback()
        print(f"Query execution error: {e}")
        raise
        
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

def execute_transaction(queries_with_params):
    """
    Execute multiple queries in a transaction
    
    Args:
        queries_with_params: List of tuples [(query1, params1), (query2, params2), ...]
    
    Returns:
        True on success, raises exception on failure
    """
    conn = None
    cursor = None
    
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        
        for query, params in queries_with_params:
            cursor.execute(query, params)
        
        conn.commit()
        return True
        
    except Exception as e:
        if conn:
            conn.rollback()
        print(f"Transaction error: {e}")
        raise
        
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

